package sg.edu.nus.iss.phoenix.schedule.dao.impl;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import sg.edu.nus.iss.phoenix.dao.DBConnection;
import sg.edu.nus.iss.phoenix.schedule.dao.WeeklyScheduleDAO;
import sg.edu.nus.iss.phoenix.schedule.entity.WeeklySchedule;

/**
 * @author Wang Shunde
 *
 */
public class WeeklyScheduleDAOImpl implements WeeklyScheduleDAO{

	private Connection connection;
	
	private static final String SQL_CREATEWEEKLYSCHEDULE = "insert into weekly_schedule_mst " +
			"(scheduled_week, scheduled_year, created_by, created_dt, modified_by, modified_dt)" +
			"values(?,?,?,?,?,?)";
	
	private static final String SQL_FINDWEEKLYSCHEDULE = "select * From  weekly_schedule_mst where " +
			"scheduled_year=? and scheduled_week=?";
	
	private static final String SEARCH_WEEKLY_SCHEDULE = "SELECT * FROM weekly_schedule_mst where scheduled_year=?";
	private static final String REMOVE_WEEKLY_SCHEDULE = "DELETE FROM weekly_schedule_mst where schedule_id=?";
	private static final String UPDATE_WEEKLY_SCHEDULE = "UPDATE weekly_schedule_mst SET scheduled_week=?, "
			+"scheduled_year=?, modified_by=?, modified_dt=? where schedule_id=?";
	
	/*
	 * (non-Javadoc)
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.WeeklyScheduleDAO#createWeeklySchedule(sg.edu.nus.iss.phoenix.schedule.entity.WeeklySchedule)
	 */
	@Override
	public long createWeeklySchedule(WeeklySchedule weeklySchedule) throws SQLException{
		openConnection();
		Date now = new java.sql.Date( Utility.now.getTime());
		PreparedStatement pstmt = null;
		long id = -1;
		try{
			pstmt = connection.prepareStatement(SQL_CREATEWEEKLYSCHEDULE, Statement.RETURN_GENERATED_KEYS);
			pstmt.setInt(1, weeklySchedule.getWeekNumber());
			pstmt.setInt(2, weeklySchedule.getYear());
			pstmt.setLong(3, weeklySchedule.getUser_id());
			pstmt.setDate(4, now);
			pstmt.setLong(5, weeklySchedule.getUser_id());
			pstmt.setDate(6, now);
			pstmt.execute();
			
			ResultSet rs = pstmt.getGeneratedKeys();
			if (rs.next()){
			    id =rs.getLong(1);
			}
		}
		finally {
			if(pstmt !=null)
				pstmt.close();
			closeConnection();
		}
		return id;
	}
	
	/*
	 * (non-Javadoc)
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.WeeklyScheduleDAO#searchMatching(int)
	 */
	@Override
	public List<WeeklySchedule> searchMatching(int selectedAnnualScheduleId) throws SQLException{
		openConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<WeeklySchedule> weeklySchedules = new ArrayList<WeeklySchedule>();
		
		try {
			ps = connection.prepareStatement(SEARCH_WEEKLY_SCHEDULE);
			ps.setInt(1, selectedAnnualScheduleId);
			rs = ps.executeQuery();
			while (rs.next()) {
				WeeklySchedule ws = new WeeklySchedule();
				ws.setId(rs.getLong("schedule_id"));
				ws.setYear(rs.getInt("scheduled_year"));
				ws.setWeekNumber(rs.getInt("scheduled_week"));
				ws.setUser_id(rs.getLong("modified_by"));
				weeklySchedules.add(ws);
			}
		}
		finally {
			if(ps !=null)
				ps.close();
			if(rs != null)
				rs.close();
			closeConnection();
		}
		
		return weeklySchedules;
	}
	
	/*
	 * (non-Javadoc)
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.WeeklyScheduleDAO#searchMatching(int, int)
	 */
	@Override
	public WeeklySchedule searchMatching(int annualScheduleId, int weekNumber) throws SQLException{
		openConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		WeeklySchedule ws = new WeeklySchedule();
		
		try {
			ps = connection.prepareStatement(SQL_FINDWEEKLYSCHEDULE);
			ps.setInt(1, annualScheduleId);
			ps.setInt(2, weekNumber);
			rs = ps.executeQuery();
			while (rs.next()) {				
				ws.setId(rs.getLong("schedule_id"));
				ws.setYear(rs.getInt("scheduled_year"));
				ws.setWeekNumber(rs.getInt("scheduled_week"));
				ws.setUser_id(rs.getLong("modified_by"));
			}
		}
		finally {
			if(ps !=null)
				ps.close();
			if(rs != null)
				rs.close();
			closeConnection();
		}
		return ws;
	}
	
	/*
	 * (non-Javadoc)
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.WeeklyScheduleDAO#updateWeeklySchedule(sg.edu.nus.iss.phoenix.schedule.entity.WeeklySchedule)
	 */
	@Override
	public void updateWeeklySchedule(WeeklySchedule ws) throws SQLException{
		openConnection();
		PreparedStatement preparedstatement = null;
		try{
			preparedstatement = connection.prepareStatement(UPDATE_WEEKLY_SCHEDULE);
			Date now = new java.sql.Date(Utility.now.getTime());
			preparedstatement.setInt(1, ws.getWeekNumber());
			preparedstatement.setInt(2, ws.getYear());
			preparedstatement.setLong(3, ws.getUser_id());
			preparedstatement.setDate(4, now);
			preparedstatement.setLong(5, ws.getId());
			preparedstatement.execute();
		}
		finally {
			if(preparedstatement !=null)
				preparedstatement.close();
			closeConnection();
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.WeeklyScheduleDAO#deleteWeeklySchedule(long)
	 */
	@Override
	public void deleteWeeklySchedule(long weeklyScheduleId) throws SQLException{
		openConnection();
		PreparedStatement ps = null;
		
		try {
			ps = connection.prepareStatement(REMOVE_WEEKLY_SCHEDULE);
			ps.setLong(1, weeklyScheduleId);
			ps.execute();
		}
		finally {
			if(ps !=null)
				ps.close();
			closeConnection();
		}
	}
	
	/**
	 * Utility method to open connection to database.
	 */
	private void openConnection() {
		try {
			DBConnection dbConnection = new DBConnection();
			this.connection = dbConnection.getDBConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	/**
	 * Utility method to close database connection.
	 */
	private void closeConnection() {
		try {
			DBConnection dbConnection = new DBConnection();
			dbConnection.closeConnection(this.connection);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
